clear all 
set more off
log using DataLog_QA_job_2018, replace
qui use abq_ices178_gu, clear
append using abq_ices179_gu
append using abq_ices180_gu
append using abq_ices181_gu
append using abq_ices182_gu
append using abq_ices183_gu
append using abq_ices184_gu
append using abq_ices185_gu
append using abq_ices186_gu
append using abq_ices187_gu
append using abq_ices188_gu
append using abq_ices189_gu
append using abq_ices190_gu
append using abq_ices191_gu
append using abq_ices192_gu
append using abq_ices193_gu
save abq_2014_18_gu, replace

destring ecec_estim, replace
drop if ecec_estim==0
destring ECEC_ESTIM, replace
drop if ECEC_ESTIM==0

//renames
replace HOURS_WORKED=ann_hours_worked if missing(HOURS_WORKED)
drop ann_hours_worked

replace CQBENC01=cqbenc01 if missing(CQBENC01)
replace CQBENC02=cqbenc02 if missing(CQBENC02)
replace CQBENC03=cqbenc03 if missing(CQBENC03)
replace CQBENC04=cqbenc04 if missing(CQBENC04)
replace CQBENC05=cqbenc05 if missing(CQBENC05)
replace CQBENC06=cqbenc06 if missing(CQBENC06)
replace CQBENC07=cqbenc07 if missing(CQBENC07)
replace CQBENC08=cqbenc08 if missing(CQBENC08)
replace CQBENC09=cqbenc09 if missing(CQBENC09)
replace CQBENC10=cqbenc10 if missing(CQBENC10)
replace CQBENC11=cqbenc11 if missing(CQBENC11)
replace CQBENC12=cqbenc12 if missing(CQBENC12)
replace CQBENC13=cqbenc13 if missing(CQBENC13)
replace CQBENC14=cqbenc14 if missing(CQBENC14)
replace CQBENC15=cqbenc15 if missing(CQBENC15)
replace CQBENC16=cqbenc16 if missing(CQBENC16)
replace CQBENC17=cqbenc17 if missing(CQBENC17)
replace CQBENC18=cqbenc18 if missing(CQBENC18)
replace CQBENC19=cqbenc19 if missing(CQBENC19)
replace CQBENC20=cqbenc20 if missing(CQBENC20)
replace CQBENC21=cqbenc21 if missing(CQBENC21)
replace CQBENC22=cqbenc22 if missing(CQBENC22)
replace CQBENC23=cqbenc23 if missing(CQBENC23)
drop cqbenc*

//Creat a column for 3dig NAICS02 (get column n3)  
qui gen n3=int(industry_code/1000)

//Creat columns of numerical variables for nonnumerical values. 
qui gen union=0
qui replace union=1 if strpos(union_nonunion,"U")

qui gen inc=0
qui replace inc=1 if strpos(time_incentive,"T") // time incentive or commission incentive 

qui gen ft=0
qui replace ft=1 if strpos(full_part_time,"F")

qui gen profit=0
qui replace profit=1 if strpos(profit_nonprofit,"P")

rename estab_employment employment

qui gen id=base_sched 
destring id, replace
drop base_sched

drop if missing(id)
drop if missing(hit_number)
qui gen idj=id*10+hit_number
drop hit_number

//Match BLS state with FIPs state
sort state_fips
save masterfile, replace

qui use bls_fips, clear
sort state_fips
save usefile, replace

qui use masterfile, clear
merge m:1 state_fips using usefile
sort _merge
drop if _merge<3
drop _merge

//Numerical collar and numerical MOG for every year and row
replace Soc_2000=soc_2000 if missing(Soc_2000)
drop soc_2000
qui gen soc2=int(Soc_2000/10000)
sort soc2
save masterfile, replace

qui use soc2_mog, clear
sort soc2
save usefile, replace

qui use masterfile, clear
merge m:1 soc2 using usefile
sort _merge
drop if _merge<3
drop _merge

save GU15_18_Q, replace

// By job in an establishment
drop if missing(id)
drop if missing(idj)
sort cycle idj
foreach a of varlist id estoccwt HOURS_WORKED ahr CQBENC* cqtotben employment union ownership profit ft n3 mognum inc ivf_grade_level bls_state_code2 state_fips census_division {
qui egen j_`a'=mean(`a'), by(cycle idj) 
}
keep cycle idj j_*
collapse j_*, by(cycle idj)
save GU15_18_Q_j, replace

//Match cycle with yrq
sort cycle
save masterfile, replace

qui use cycle_yrq, clear
sort cycle
save usefile, replace

qui use masterfile, clear
merge m:1 cycle using usefile
sort _merge
drop if _merge<3
drop _merge
save GU15_18_Q_j, replace


//=========to make sure only real n3 shows up==========
rename j_n3 n3
sort n3
save masterfile, replace

qui use naics2002_n3, clear 
sort n3
save usefile, replace

qui use masterfile, clear
merge m:1 n3 using usefile
sort _merge
drop if _merge<3
drop _merge

//n3, n2, n1
drop if missing(n3)
qui gen n2=int(n3/10)
qui gen n1=int(n3/100)
tabulate n1
tabulate n2
tabulate n3

qui tab n1, gen(n1_)

//========to make sure only real states shows up===========
sort j_bls_state_code2
save masterfile, replace

qui use bls_states, clear
sort j_bls_state_code2
save usefile, replace

qui use masterfile, clear
merge m:1 j_bls_state_code2 using usefile
sort _merge
drop if _merge<3
drop _merge

tabulate j_bls_state_code2

//========to make sure only real mog shows up===========
sort j_mognum
save masterfile, replace

qui use mognum_2017, clear 
sort j_mognum
save usefile, replace

qui use masterfile, clear
merge m:1 j_mognum using usefile
sort _merge
drop if _merge<3
drop _merge

tabulate j_mognum
qui tab j_mognum, gen(femog_)

save GU15_18_Q_j, replace // 

//Get all annual data 
qui keep if cycle == 180 | cycle == 184 | cycle == 188 | cycle == 192
save GU15_18_A_j, replace // 
log close
